<?php

// Include shared script

require_once('shared.php');


// ---------- DATABASE STATEMENTS ----------

// Imported data

$stmtImport1 = $db->query(
	'SELECT			*
	FROM			sherpa_importData
	WHERE			ready = 1
	ORDER BY		id ASC'
);

$stmtImport2 = $db->prepare(
	'UPDATE			sherpa_importData
	SET				productId = ?,
					parentId = ?,
					imported = NOW()
	WHERE			id = ?'
);

// Products

$stmtProd1 = $db->prepare(
	'UPDATE		sherpa_products
	SET			imported = NULL'
);

$stmtProd2 = $db->prepare(
	'SELECT			id
	FROM			sherpa_products
	WHERE			articleNumber = ?
	AND				eanCode IS NULL'
);

$stmtProd3 = $db->prepare(
	'SELECT			id
	FROM			sherpa_products
	WHERE			articleNumber = ?
	AND				eanCode = ?'
);

$stmtProd4 = $db->prepare(
	'UPDATE			sherpa_products
	SET				productName = ?,
					productLine = ?,
					productText = ?,
					features = ?,
					specifications = ?,
					sizeTableId = ?,
					available = ?,
					availabilityText = ?,
					active = ?,
					actualStock = ?,
					sizeInfo = ?,
					archived = NULL
	WHERE			id = ?'
);

$stmtProd5 = $db->prepare(
	'INSERT INTO	sherpa_products
					(languageId,
					parentId,
					articleNumber,
					eanCode,
					productName,
					productLine,
					productText,
					features,
					specifications,
					sizeTableId,
					available,
					availabilityText,
					active,
					actualStock,
					sizeInfo,
					archived,
					created)
	VALUES			(6, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NOW())'
);

$stmtProd6 = $db->prepare(
	'UPDATE			sherpa_products
	SET				imported = NOW()
	WHERE			id = ?'
);

$stmtProd7 = $db->prepare(
	'UPDATE			sherpa_products
	SET				archived = NOW()
	WHERE			imported IS NULL'
);

// Categories

$stmtCat1 = $db->prepare(
	'DELETE FROM	sherpa_productToCategory
	WHERE			parentProductId = ?'
);

$stmtCat2 = $db->prepare(
	'INSERT INTO	sherpa_productToCategory
					(parentProductId,
					categoryId,
					created)
	VALUES			(?, ?, NOW())'
);

// Sizes

$stmtSize1 = $db->prepare(
	'DELETE FROM	sherpa_productToSize
	WHERE			parentProductId = ?'
);

$stmtSize2 = $db->prepare(
	'SELECT			id
	FROM			sherpa_sizes
	WHERE			size = ?'
);

$stmtSize3 = $db->prepare(
	'INSERT INTO	sherpa_sizes
					(size,
					sort,
					created)
	VALUES			(?, ?, NOW())'
);

$stmtSize4 = $db->prepare(
	'SELECT			parentProductId
	FROM			sherpa_productToSize
	WHERE			parentProductId = ?
	AND				sizeId = ?'
);

$stmtSize5 = $db->prepare(
	'INSERT INTO	sherpa_productToSize
					(parentProductId,
					sizeId,
					created)
	VALUES			(?, ?, NOW())'
);

$stmtSize6 = $db->prepare(
	'UPDATE			sherpa_products
	SET				sizeId = ?
	WHERE			id = ?'
);

// Colors

$stmtColor1 = $db->prepare(
	'DELETE FROM	sherpa_productToColorApplication
	WHERE			parentProductId = ?'
);

$stmtColor2 = $db->prepare(
	'SELECT			id
	FROM			sherpa_colors
	WHERE			colorCode = ?
	AND				rgbRed = ?
	AND				rgbGreen = ?
	AND				rgbBlue = ?'
);

$stmtColor3 = $db->prepare(
	'INSERT INTO	sherpa_colors
					(colorSetId,
					colorCode,
					colorName,
					rgbRed,
					rgbGreen,
					rgbBlue,
					created)
	VALUES			(?, ?, ?, ?, ?, ?, NOW())'
);

$stmtColor4 = $db->prepare(
	'SELECT			id
	FROM			sherpa_colorApplications
	WHERE			primaryColorId = ?
	AND				secondaryColorId = ?'
);

$stmtColor5 = $db->prepare(
	'SELECT			id
	FROM			sherpa_colorApplications
	WHERE			primaryColorId = ?
	AND				secondaryColorId IS NULL'
);

$stmtColor6 = $db->prepare(
	'INSERT INTO	sherpa_colorApplications
					(primaryColorId,
					secondaryColorId,
					created)
	VALUES			(?, ?, NOW())'
);

$stmtColor7 = $db->prepare(
	'SELECT			parentProductId
	FROM			sherpa_productToColorApplication
	WHERE			parentProductId = ?
	AND				colorApplicationId = ?'
);

$stmtColor8 = $db->prepare(
	'INSERT INTO	sherpa_productToColorApplication
					(parentProductId,
					colorApplicationId,
					created)
	VALUES			(?, ?, NOW())'
);

$stmtColor9 = $db->prepare(
	'UPDATE			sherpa_products
	SET				colorApplicationId = ?
	WHERE			id = ?'
);

$stmtColor10 = $db->prepare(
	'SELECT			id
	FROM			sherpa_colorSets
	WHERE			colorSet = ?'
);

// Prices

$stmtPrice1 = $db->prepare(
	'DELETE FROM	sherpa_prices
	WHERE			productId = ?'
);

$stmtPrice2 = $db->prepare(
	'INSERT INTO	sherpa_prices
					(productId,
					currencyId,
					price,
					oldPrice,
					created)
	VALUES			(?, 1, ?, ?, NOW())'
);

// Icons

$stmtIcon1 = $db->prepare(
	'DELETE FROM	sherpa_productToIcon
	WHERE			parentProductId = ?'
);

$stmtIcon2 = $db->prepare(
	'INSERT INTO	sherpa_productToIcon
					(parentProductId,
					iconId,
					sort,
					created)
	VALUES			(?, ?, ?, NOW())'
);



// ---------- IMPORT START ----------

// Set imported date of all product records to null
$stmtProd1->execute();

// Walk through all import records
$lastArticleNumber = null;
$parentId = null;

while ($importData = $stmtImport1->fetch(PDO::FETCH_ASSOC)) {

	// If article number changed, this must be a parent product
	if ($importData['articleNumber'] != $lastArticleNumber) {
		$lastArticleNumber = $importData['articleNumber'];
		$parent = true;
		$parentId = null;
	}
	else {
		$parent = false;
	}


	// ---------- PRODUCTS ----------

	$productId = null;

	// Check if product already exists in database
	// If we process a parent product
	if ($parent) {
		$stmtProd2->execute(array(
			$importData['articleNumber']
		));

		// If product exists in database, retrieve its id
		if ($row = $stmtProd2->fetch(PDO::FETCH_ASSOC)) {
			$productId = $row['id'];
		}
	}

	// If we process a child product
	else {
		$stmtProd3->execute(array(
			$importData['articleNumber'],
			$importData['eanCode']
		));

		// If product exists in database, retrieve its id
		if ($row = $stmtProd3->fetch(PDO::FETCH_ASSOC)) {
			$productId = $row['id'];
		}
	}

	// If product exists in database, update it with imported data
	if ($productId != null) {
		$stmtProd4->execute(array(
			$importData['productName'],
			$importData['productLine'],
			$importData['productText'],
			$importData['features'],
			$importData['specifications'],
			$importData['sizeTableId'],
			$importData['available'],
			$importData['availabilityText'],
			$importData['active'],
			$importData['actualStock'],
			$importData['sizeInfo'],
			$productId
		));
	}

	// If product does NOT exist in database, insert it
	else {
		$stmtProd5->execute(array(
			$parentId,
			$importData['articleNumber'],
			$importData['eanCode'],
			$importData['productName'],
			$importData['productLine'],
			$importData['productText'],
			$importData['features'],
			$importData['specifications'],
			$importData['sizeTableId'],
			$importData['available'],
			$importData['availabilityText'],
			$importData['active'],
			$importData['actualStock'],
			$importData['sizeInfo']
		));

		// Store id of inserted product
		$productId = $db->lastInsertId();
	}

	// If we process a parent product, store its id so that its children can refer to it
	if ($parent) {
		$parentId = $productId;
	}


	// ---------- CATEGORIES ----------

	// If we process a parent product
	if ($parent) {
		$categories = split(',', $importData['categoryIds']);

		// Delete all parent-category assignments
		$stmtCat1->execute(array(
			$productId
		));

		// Assign category to parent
		foreach ($categories as $categoryId) {
			$stmtCat2->execute(array(
				$productId,
				$categoryId
			));
		}
	}


	// ---------- SIZES ----------

	// If we process a parent product
	if ($parent) {
		// Delete all parent-size assignments
		$stmtSize1->execute(array(
			$productId
		));
	}

	// If we process a child product
	else {
		// Check if size already exists in database
		$stmtSize2->execute(array(
			$importData['size']
		));

		// If size exists in database, store its id
		if ($row = $stmtSize2->fetch(PDO::FETCH_ASSOC)) {
			$sizeId = $row['id'];
		}

		// If size is NOT yet in database, insert it and store its id
		else {
			$stmtSize3->execute(array(
				$importData['size'],
				$importData['size'] // field size = default for field sort
			));
			$sizeId = $db->lastInsertId();
		}

		// Check if size has been assigned to parent previously
		$stmtSize4->execute(array(
			$parentId,
			$sizeId
		));

		// If size has NOT been assigned to parent, assign it
		if (!$stmtSize4->fetch(PDO::FETCH_ASSOC)) {
			$stmtSize5->execute(array(
				$parentId,
				$sizeId
			));
		}

		// Update child product with sizeId
		$stmtSize6->execute(array(
			$sizeId,
			$productId
		));
	}


	// ---------- COLORS ----------

	// If we process a parent product
	if ($parent) {
		// Delete all color application assignments
		$stmtColor1->execute(array(
			$productId
		));
	}

	// If we process a child product
	else {
		// Check if color 1 already exists in database
		$stmtColor2->execute(array(
			$importData['colorCode1'],
			$importData['rgbRed1'],
			$importData['rgbGreen1'],
			$importData['rgbBlue1']
		));

		// If color 1 exists in database, store its id
		if ($row = $stmtColor2->fetch(PDO::FETCH_ASSOC)) {
			$colorId1 = $row['id'];
		}

		// If color 1 is not yet in database, insert it and store its id
		else {
			// Get color set id
			$stmtColor10->execute(array(
				$importData['colorSet1'],
			));
			$row = $stmtColor10->fetch(PDO::FETCH_ASSOC);
			$colorSetId = $row['id'];
		
			$stmtColor3->execute(array(
				$colorSetId,
				$importData['colorCode1'],
				$importData['colorName1'],
				$importData['rgbRed1'],
				$importData['rgbGreen1'],
				$importData['rgbBlue1']
			));

			$colorId1 = $db->lastInsertId();
		}

		// If product has a second color
		if ($importData['colorCode2'] != null) {
			// Check if color 2 already exists in database
			$stmtColor2->execute(array(
				$importData['colorCode2'],
				$importData['rgbRed2'],
				$importData['rgbGreen2'],
				$importData['rgbBlue2']
			));

			// If color 2 exists in database, store its id
			if ($row = $stmtColor2->fetch(PDO::FETCH_ASSOC)) {
				$colorId2 = $row['id'];
			}

			// If color 2 is not yet in database, insert it and store its id
			else {
			// Get color set id
			$stmtColor10->execute(array(
				$importData['colorSet2'],
			));
			$row = $stmtColor10->fetch(PDO::FETCH_ASSOC);
			$colorSetId = $row['id'];
			
				$stmtColor3->execute(array(
					$colorSetId,
					$importData['colorCode2'],
					$importData['colorName2'],
					$importData['rgbRed2'],
					$importData['rgbGreen2'],
					$importData['rgbBlue2']
				));

				$colorId2 = $db->lastInsertId();
			}
		}

		// If product has NO second color, set corresponding color id to null
		else {
			$colorId2 = null;
		}

		// Check if color application already exists in database
		$colorApplicationId = null;

		// For products with 2 colors
		if ($colorId2 != null) {
			$stmtColor4->execute(array(
				$colorId1,
				$colorId2
			));

			// If color application exists in database, retrieve its id
			if ($row = $stmtColor4->fetch(PDO::FETCH_ASSOC)) {
				$colorApplicationId = $row['id'];
			}
		}

		// For products with 1 color
		else {
			$stmtColor5->execute(array(
				$colorId1
			));

			// If color application exists in database, retrieve its id
			if ($row = $stmtColor5->fetch(PDO::FETCH_ASSOC)) {
				$colorApplicationId = $row['id'];
			}
		}

		// If color application is not yet in database, insert it
		if ($colorApplicationId == null) {
			$stmtColor6->execute(array(
				$colorId1,
				$colorId2
			));

			$colorApplicationId = $db->lastInsertId();
		}

		// Check if color application has been assigned to parent previously
		$stmtColor7->execute(array(
			$parentId,
			$colorApplicationId
		));

		// If color has NOT been assigned to parent, assign it
		if (!$stmtColor7->fetch(PDO::FETCH_ASSOC)) {
			$stmtColor8->execute(array(
				$parentId,
				$colorApplicationId
			));
		}

		// Update child product with color application id
		$stmtColor9->execute(array(
			$colorApplicationId,
			$productId
		));
	}


	// ---------- PRICES ----------

	// Delete all product prices
	$stmtPrice1->execute(array(
		$productId
	));

	// Add product price
	$stmtPrice2->execute(array(
		$productId,
		$importData['priceCHF'],
		$importData['oldPriceCHF']
	));


	// ---------- ICONS ----------

	if ($parent) {
		// Delete all parent product to icon assignments
		$stmtIcon1->execute(array(
			$productId
		));

		// Insert new parent product to icon assignments
		$iconIds = split(',', $importData['iconIds']);
		foreach ($iconIds as $i => $iconId) {
			$stmtIcon2->execute(array(
				$productId,
				$iconId,
				$i + 1
			));
		}
	}


	// ---------- FINALIZE PRODUCT ----------

	// Update product with import timestamp
	$stmtProd6->execute(array(
		$productId
	));

	// Update import data with timestamp, product id and parent id
	$stmtImport2->execute(array(
		$productId,
		$parentId != $productId ? $parentId : null,
		$importData['id']
	));
}


// Mark products as archived that were not included in import
//$stmtProd7->execute();

// ---------- IMPORT END ----------